The DataGrid Control

Probably the most usual way to display data in a database table is with a grid control. Visual Basic 6 comes with several grid controls, but only two of them can work with the newer ADO Data control and other ADO data sources: the DataGrid control and the Hierarchical FlexGrid control. I'll describe the DataGrid control in this section and the Hierarchical FlexGrid control in the next section.

Before looking at the individual properties, methods, and events supported by the DataGrid control, you should be familiar with its object model. As you can see in Figure 15-2, this is a simple object model, with the DataGrid control at the top of the hierarchy and the Columns and Splits collections under it. You can split a DataGrid control into two or more sections and navigate through them independently or in a synchronized manner. The DataGrid control is included in the MSDATGRD.OCX file, which must therefore be distributed with any application that uses this control.

Figure 15-2. The object model for the DataGrid control.

Since the DataGrid control can work only as a bound control to an ADO data source, the first thing to do is prepare such a source. This can be a design-time source such as an ADO Data control or a DataEnvironment object, or it can be a run-time source such as an ADO Recordset or an instance of a custom class that qualifies as a data source. Working with design-time sources is definitely preferable, because you can retrieve the field structure at design time and adjust column width and other attributes in a visual manner, without writing code.

After you have bound the DataGrid control to an ADO Data control or to a DataEnvironment's Command object through the DataGrid's DataSource property, you can right-click on the control and select the Retrieve Fields menu command. This prepares a column layout at design time, with each column taking its caption and width directly from the database field to which it maps. You can then right-click again on the control and select the Edit menu command, which puts the grid in edit mode. In this mode, you can adjust the column width, scroll the grid horizontally by using the scroll bar at the bottom, and right-click on the control to display a menu of commands. These commands allow you to add and remove columns, split the grid into two or more sections, cut and paste columns to rearrange their order, and so on. To modify other properties, however, you must right-click once again on the control and select the Properties command, which brings up a Property Pages dialog box with as many as eight tabs, as shown in Figure 15-3.

Contrary to what the documentation states, it seems impossible in practice to have distinct column layouts for different split sections. In fact, if you delete an existing column or add a new column to a split, all the other splits are affected as well. A possible workaround for this problem is to set a column's Visible property to False. Because this attribute can be set on a split-by-split basis (as explained in "The Layout Tab" section later in this chapter), you can effectively hide a column in all the splits where it shouldn't appear.

Figure 15-3. The DataGrid control at design time, after displaying its Property Pages dialog box.

By default, the grid has no caption, but you can enter a custom string in the General tab of the Property Pages dialog box; if a nonempty string is specified for the Caption property, it will appear in a gray section above the column headers. The AllowAddNew, AllowDelete, and AllowUpdate Boolean properties determine which operations are allowed on the grid. The ColumnHeaders property can be set to False to hide the gray row containing the column headers. Note that in the Font tab you can set the HeadFont property, which determines the character font used for column headers.

The DefColWidth property is the default width of the grid's columns: If set to 0 (its default value), the width of each column is the largest value between the underlying field's size and the column header's width. The HeadLines property is an integer between 0 and 10, and corresponds to the number of rows used for column headings; you can use 0 to remove column headers, but it's preferable to set the ColumnHeaders property to False to achieve the same result. The RowHeight property is the height of each row in twips. The DataGrid control doesn't support rows of different heights.

You can set the BorderStyle property to 0-dbgNoBorder to suppress the fixed border around the grid. The RowDividerLine property determines the style used to draw the dividing lines between the rows and can be one of the following enumerated values: 0-dbgNoDividers, 1-dbgBlackLine, 2-dbgDarkGrayLine (the default), 3dbgRaised, 4-dbgInset, or 5-dbgUseForeColor. If 3-dbgRaised or 4-dbgInset is used, the color of the dividing line depends on the Microsoft Windows settings.

The Keyboard tab allows you to set some properties that affect how keys behave when the DataGrid control has the focus. If the AllowArrows property is True, the user can visit all the cells in the grid using the arrow keys; if WrapCellPointer is also True, pressing the right arrow key at the end of a row moves the focus rectangle to the first cell in the next row and pressing the left arrow key at the beginning of a row moves the focus rectangle to the last cell in the previous row.

The TabAction property decides what happens when the Tab key or Shift+Tab key combination is pressed and the DataGrid control is the active control. The default action is 0dbgControlNavigation, in which case the next control (or the previous control, if Shift+Tab is pressed) on the form receives the focus. If you set this property to 1dbgColumnNavigation, pressing the Tab key moves the focus rectangle to the next column unless the current cell is the last (or the first, if Shift+Tab is pressed) of its row. In this case, pressing this key causes the focus to move to the next (or previous) control in the TabIndex order. Finally, the setting 2-dbgGridNavigation is similar to the previous one, but the Tab key never moves the focus rectangle to another control and the behavior at the beginning or end of the row depends on the WrapCellPointer property.

By default, tab and arrow keys never move the focus rectangle to another split in the same grid. You can, however, set the TabAcrossSplit property to True to let the user navigate through splits by using the Tab key. In this case, the value of the WrapCellPointer and TabAction properties are ignored, unless the user presses the Tab key when the current cell is in the last column of the rightmost split or presses the Shift+Tab key combination when the current cell is in the first column of the leftmost split.

The Columns tab allows you to set the Caption property of each individual Column object, as well as its DataField property, which contains the name of the field in the data source to which the column is bound.

The Format tab allows you to set the DataFormat property of each Column object, using the same dialog box used for individual bound controls. Typically, you use this tab to format numbers, currency values, dates, and times. You can also use a custom format, if needed. The settings on this tab are reflected in the DataFormat property of individual Column objects at run time. A few other properties of Column objects, which will be described later, are set on the Layout tab.

If the grid is subdivided into two or more split areas, you can set the attributes for these areas in the Splits tab. You can't create new splits in this property page, but you act on the fields in this page to set each split's appearance and behavior. (Creating a new split is described in the "Editing the Column Layout" section earlier in this chapter.)

To modify the attributes of a split, you have to select it in the upper drop-down list. If the grid isn't split, there will be only one item in the drop-down list, the Split 0 item, and your setting will affect the entire grid control. You can set the Locked property to True to turn the DataGrid into a read-only control. The AllowFocus property determines whether the split can receive the focus (it's similar to the TabStop property of individual Visual Basic controls). The AllowSizing property determines whether the split can be interactively resized with the mouse at run time. If AllowRowResizing is True, the user can resize rows in this split by using the mouse. (Resize operations affect all the rows in all the splits because the DataGrid control doesn't support rows with different heights.) The RecordSelectors property determines whether there is a gray column for displaying record selectors on the left side of the split (or the whole grid).

You can control whether multiple splits vertically scroll together or independently of one another by using the ScrollGroup property of the Split object, which is an integer greater than or equal to 1. All the splits with the same value scroll together, so you can create splits that scroll independently by assigning different values to this property. The ScrollBars property affects the presence or absence of scroll bars in a particular split and takes one of the following values: 0-dbgNone, 1dbgHorizontal, 2-dbgVertical, 3-dbgBoth, and 4-dbgAutomatic. (The default is 4dbgAutomatic—show a scroll bar only if necessary.) If you have a group of Split objects that scroll together and the ScrollBars property of each is set to 4dbgAutomatic, only the rightmost split of the group will show a vertical scroll bar.

The MarqueeStyle property determines how the DataGrid control highlights the currently selected cell. This property can have one of the following values: 0dbgDottedCellBorder (a dotted border around the cell, also known as a focus rectangle, is used), 1-dbgSolidCellBorder (a solid border is used, which is usually more visible than a dotted border), 2-dbgHighlightCell (text and background color are inverted), 3-dbgHighlightRow (the entire row is highlighted—this is useful only when the grid or the split isn't editable), 4-dbgHighlightRowRaiseCell (similar to the previous one, but the current cell appears to be raised), 5-dbgNoMarquee (the current cell isn't highlighted in any way), or 6-dbgFloatingEditor (the default—the current cell is highlighted using a floating editor window with a blinking cursor, as in Microsoft Access).

The AllowRowSizing, MarqueeStyle, and RecordSelectors properties are exposed by the DataGrid control as well as its Split objects. Setting one of these properties for the DataGrid control has the same effect as setting the same property for all its Split objects.

The last two properties shown in the Splits tab work together to determine how many columns are visible in the split and whether they are resized to fit in the visible area. More precisely, the Size property can be assigned a numeric value whose meaning depends on the SizeMode property. If SizeMode is 0-dbgScalable, Size contains an integer that corresponds to the width of that split with respect to other scalable splits; for example, if you have two splits with Size = 1 and Size = 2, respectively, the first split will take one third of the grid's width and the second split will take the remaining two thirds. If SizeMode is 1-dbgExact, then Size is a floating-point number that corresponds to the split's exact width in twips; this setting ensures that the split always has the same width, whether other splits are added or removed.

In the Layout tab, you can set column attributes on a split-by-split basis. The DataGrid control, in fact, allows you to display the same column with different attributes in different splits. For example, a column can be read-write in one split and read-only in another; or it can be invisible in some of the splits and visible in others. You set the read-only attribute with the Locked property and the visibility attribute with the Visible property. The AllowSizing Boolean property determines if the right border of the column can be dragged to resize the column's width. The WrapText Boolean property causes the text in the cell to wrap to the next row if necessary: You can use this property with the RowHeight property to produce multiline displays. The Button property, if set to True, causes a button for a drop-down menu to appear in the cell when it gets the focus. When the user clicks on this button, the DataGrid control receives a ButtonClick event, to which you typically react by dropping down a list of values using a standard ComboBox, a bound ListBox, or even another DataGrid control.

The DividerStyle property affects the style of the vertical line on the right border of a column and can be one of the following values: 0-dbgNoDividers, 1dbgBlackLine, 2-dbgDarkGrayLine (the default), 3-dbgRaised, 4-dbgInset, 5dbgUseForeColor, or 6-dbgLightGrayLine. The Alignment property sets the alignment of the contents of the column and can be 0-dbgLeft, 1-dbgRight, 2-dbgCenter, or 3-dbgGeneral. (By default, text is left-aligned and numbers are right-aligned.) The Width property specifies the width of each Column object, expressed in the units of the DataGrid's container.

The DataGrid control is complex and is likely to demand some time from you before you're familiar with it. I'll outline the most common operations that you might want to perform on it, together with a few tricks to get the most out of this object.

The most important run-time properties of the DataGrid control are Row and Col, which set or return the position of the cell in the focus rectangle. The first row and the leftmost column return zero values. Once you make a given cell the current cell, you can retrieve and modify its contents using the DataGrid's Text property:

' Convert the current cell's contents to uppercase. Private Sub cmdUppercase_Click() DataGrid1.Text = UCase$(DataGrid1.Text) End Sub

The EditActive property returns True if the current cell is being edited and False otherwise; you can also assign a value to this property to enter or exit edit mode programmatically. When the edit mode is entered, a ColEdit event is triggered:

' Save the current cell value before editing. Private Sub DataGrid1_ColEdit(ByVal ColIndex As Integer) ' SaveText is a module-level variable. SaveText = DataGrid1.Text End Sub

You can determine whether the current cell has been modified by querying the CurrentCellModified property, and you can also set this property to False and then set EditActive to False to completely cancel the edit operation. The CurrentCellVisible property is exposed by both the DataGrid and Split objects; it returns True if the current cell is visible in the object. If you set a Split's CurrentCellVisible property to True, the Split scrolls until the cell becomes visible; if you set the DataGrid control's CurrentCellVisible property to True, all the splits scroll to make the cell visible. While the current cell is being edited, you can also read and modify the grid's SelStart, SelLength, and SelText properties, as you would do with a regular TextBox control.

Because the DataGrid control is always bound to an ADO data source, the Bookmark property, which sets or returns the bookmark to the current record, is often more useful than the Row property. Even more interesting, whenever the user moves to another row, the current record in the underlying Recordset object automatically changes to reflect the new current cell. Thus, you can retrieve additional fields from the Recordset by simply querying the Recordset's Fields collection. The following code assumes that the DataGrid control is bound to an ADO Data control:

' Display the current product's unit price in Euro currency. ' The RowColChange event fires when a new cell becomes current. Private Sub DataGrid1_RowColChange(LastRow As Variant, _ ByVal LastCol As Integer) ' The DOLLAR_TO_EURO_RATIO variable is defined elsewhere in the module. lblEuroPrice = Adodc1.Recordset("UnitPrice") * DOLLAR_TO_EURO_RATIO End Sub

The DataGrid control's Split property returns an integer in the range 0 through Splits.Count-1, which points to the split section that contains the current cell. You can also assign a new value to this property to move the focus to another split. When a grid is split into more sections, a few properties of the DataGrid control—such as RecordSelectors and FirstRow—are equivalent to the same properties exposed by the current split. In other words:

' The following statements are equivalent. DataGrid1.RecordSelectors = True DataGrid1.Splits(DataGrid1.Split).RecordSelectors = True

There are a few properties that let you retrieve and set the properties of any cell in the grid, but you have to use them in a way that isn't always intuitive. Each column object exposes the Text and Value properties: The former sets or returns the text displayed in the column for the current row, while the latter is the actual value in the column for the current row before it's formatted for display to the user. The Column object also exposes the CellText and CellValue methods, which return the contents of a cell in that column for any row, given its bookmark. There are several ways to retrieve the bookmark relative to a row, as I'll show you in a moment.

VisibleRows and VisibleCols are read-only properties that return the number of visible rows and columns, respectively. There are no properties that directly return the total number of rows and columns. You can use the ApproxCount property, which returns the approximate number of rows; this number might differ from the actual value. To retrieve the number of columns, you must query the Count property of the Columns collection.

The DataGrid object exposes two methods that let you access the bookmark of any row in the control. GetBookmark returns a bookmark of a row relative to the current row: GetBookmark(0) is the same as the Bookmark property, GetBookmark(-1) is the bookmark of the row preceding the current row, GetBookmark(1) is the bookmark of the row following the current row, and so on. The other available method, RowBookmark, returns the bookmark of any visible row: RowBookmark(0) is the bookmark of the first visible row, and RowBookmark(VisibleRows-1) is the bookmark of the last visible row.

The bookmark of the first row is also returned by the FirstRow property. According to the documentation, you can assign a new bookmark to this property to programmatically scroll the grid's contents, but I found that I always get an "Invalid bookmark" error when I try to assign a value to it. The LeftCol property holds the index of the first visible column, so you can programmatically display the upper left corner of the grid using the code shown below.

DataGrid1.LeftCol = 0 Adodc1.Recordset.MoveFirst DataGrid1.CurrentCellVisible = True

The FirstRow, LeftCol, and CurrentCellVisible properties are also exposed by the Split object; here, also, assigning a value to the FirstRow property without raising an error appears impossible.

You can use the value returned by any of the preceding bookmark methods as an argument of the Column object's CellText and CellValue methods, described previously. For example, this code displays the difference in the Total field between the current row and the row that precedes the current row:

Private Sub DataGrid1_RowColChange(LastRow As Variant, _ ByVal LastCol As Integer) Dim gcol As MSDataGridLib.Column If DataGrid1.Row > 0 Then ' Get a reference to the current column. Set gcol = DataGrid1.Columns("Total") ' Display the difference between the values in the "Total" column ' of the current row and the cell immediately above it. Label1 = gcol.CellValue(DataGrid1.GetBookmark(-1)) - gcol.Value Else Label1 = "(First Row)" End If End Sub

Users can select any number of adjacent columns by clicking on the column headers while keeping the Shift key pressed; they can also select any number of rows—even nonadjacent ones—by clicking on the leftmost gray column while keeping the Ctrl key pressed. (Multiple row selection, therefore, requires that the grid's or the split's RecordSelectors property is set to True.) The SelStartCol and SelEndCol properties set and return the indices for the first and last selected columns, respectively. You can clear the column selection by setting these properties to -1, or by invoking the ClearSelCols method. These properties and this method are also exposed by the Split object.

Because the user can select nonadjacent rows, the system to determine which rows are currently highlighted is based on the DataGrid control's SelBookmarks collection, which contains the bookmarks of all the selected rows. For example, to select the current row, execute the following statement:

DataGrid1.SelBookmarks.Add DataGrid1.Bookmark

You can iterate on all the selected rows using a For Each loop. For example, the following code takes advantage of the SelChange event—which fires any time a column or a row is selected or deselected—to update a Label control with the sum of all the cells in the Total column for the rows that are currently selected:

Private Sub DataGrid1_SelChange(Cancel As Integer) Dim total As Single, bmark As Variant For Each bmark In DataGrid1.SelBookmarks total = total + DataGrid.Columns("Total").CellValue(bmark) Next lblGrandTotal = total End Sub

There's no method that programmatically clears selected rows; you can do this only by removing all the items in the SelBookmark collection, as in the following code:

Do While DataGrid1.SelBookmarks.Count DataGrid1.SelBookmarks.Remove 0 Loop

The DataGrid control has a rich collection of events that let you trap nearly every user action. Almost all these events are in the form Beforexxxx and Afterxxxx, where Beforexxxx events receive a Cancel parameter that you can set to True to cancel the operation. We've already seen the ColEdit event, which fires whenever a value in a cell is edited by pressing a key. This event is actually preceded by the related BeforeColEdit event, which lets you selectively make a cell read-only:

' Refuse to edit a cell in the first column if it already contains a value. Private Sub DataGrid1_BeforeColEdit(ByVal ColIndex As Integer, _ ByVal KeyAscii As Integer, Cancel As Integer) ' Note how you can test Null values and empty strings at the same time. If ColIndex = 0 And DataGrid1.Columns(ColIndex).CellValue _ (DataGrid1.Bookmark) & "" <> "" Then Cancel = True End If End Sub

If you cancel the edit operation in the BeforeColEdit event, the control doesn't receive any other event for this operation, which might be disorienting if you're accustomed to the ADO way of raising events, where a postnotification event fires even if the code in the prenotification event cancels the operation. The KeyAscii parameter contains the code of the key pressed to enter edit mode, or 0 if the user entered edit mode with a click of the mouse. Because this parameter is passed by value, you can't alter it. This isn't a problem, however, because the grid also receives all the usual KeyDown, KeyPress, and KeyUp events, which let you modify the value of the parameter that contains the code for the key the user pressed.

Any time you modify a value in a cell, the DataGrid control receives a Change event; if the edit operation actually modifies the value in a cell—that is, if you don't cancel it with the Esc key—the control also receives the BeforeColUpdate and AfterColUpdate events:

Private Sub DataGrid1_BeforeColUpdate(ByVal ColIndex As Integer, _ OldValue As Variant, Cancel As Integer) ' Trap invalid values here. End Sub

But watch out for a quirk in the procedure. You can't access the value that is about to be entered in the grid by using the Text or Value properties of the DataGrid or the Column, because within this event procedure these properties return the value that was originally in the grid cell—that is, the same value returned by the OldValue parameter. It turns out that the DataGrid's Text property returns the string entered by the user only when the EditActive property is True, but this property has already been reset to False when processing the BeforeColUpdate event. The solution is to declare a form-level variable and assign it a value from within the Change event. For example, this code correctly checks that the value being entered isn't duplicated in any other record of the Recordset:

Dim newCellText As String ' Remember the most recent value entered by the user. Private Sub DataGrid1_Change() newCellText = DataGrid1.Text End Sub ' Check that the user isn't entering a duplicate value for that column. Private Sub DataGrid1_BeforeColUpdate(ByVal ColIndex As Integer, _ OldValue As Variant, Cancel As Integer) Dim rs As ADODB.Recordset, fldName As String ' Retrieve the field name for the current column. fldName = DataGrid1.Columns(ColIndex).DataField ' Search for the new value in the Recordset. Use a clone Recordset ' so that the current bookmark doesn't change. Set rs = Adodc1.Recordset.Clone rs.MoveFirst rs.Find fldName & "='" & newCellValue & "'" ' Cancel the operation if a match has been found. If Not rs.EOF Then Cancel = True End Sub

When the user moves to another row, a pair of BeforeUpdate and AfterUpdate events fire, and you have an opportunity to perform record-level validation and optionally reject the update. Here's the complete sequence of events that fire when the user edits a value in a column and then moves to the next or previous grid row:

KeyDown	The user presses a key.
KeyPress
BeforeColEdit	The grid enters edit mode.
ColEdit
Change	Now you can read the new value using the Text property. Here the ActiveEdit property becomes True.
KeyUp	The first key is released.
KeyDown	Another key is pressed.
KeyPress
Change
KeyUp
	Other keys are pressed.
BeforeColUpdate	The user moves to another column.
AfterColUpdate
AfterColEdit
RowColChange	This event fires only when the move is complete.
BeforeUpdate	The user moves to another row.
AfterUpdate
RowColChange	This event fires only when the move is complete.

The user can delete one or more rows by selecting them and then pressing the Delete key. This operation fires the BeforeDelete event (where you can cancel the command) and AfterDelete event, and then a BeforeUpdate and AfterUpdate pair of events. For example, you can write code in the BeforeDelete event procedure that checks whether the current record is the master record in a master-detail relationship, and either cancels the operation (as the following code illustrates) or automatically deletes all the related detail records.

Private Sub DataGrid1_BeforeDelete(Cancel As Integer) Dim rs As ADODB.Recordset, rsOrderDetails As ADODB.Recordset ' Get a reference to the underlying Recordset Set rs = Adodc1.Recordset ' Use the connection to perform a SELECT command that checks whether ' there is at least one record in the Order Details table that has ' a foreign key that points to the ProductID value of current record. Set rsOrderDetails = rs.ActiveConnection.Execute _ ("Select * FROM [Order Details] WHERE [Order Details].ProductID=" _ & rs("ProductID")) ' If EOF = False, there is a match, so cancel the delete command. If Not rsOrderDetails.EOF Then Cancel = True End Sub

If you cancel the delete command, the DataGrid control displays an error message. You can suppress this and other error messages from the control by trapping its Error event:

Private Sub DataGrid1_Error(ByVal DataError As Integer, _ Response As Integer) ' DataError = 7011 means "Action canceled" If DataError = 7011 Then MsgBox "Unable to delete this record because there are " _ & "records in the Order Details table that point to it." ' Cancel the standard error processing by setting Response = 0. Response = 0 End If End Sub

Upon entry into this event, the DataError parameter contains the error code, whereas the Response parameter contains 1; you can prevent the grid from displaying the standard error message by setting the Response parameter to 0, as the previous example demonstrates. You can also test the standard error message by means of the DataGrid's ErrorText property.

If the AllowAddNew property is True, the DataGrid control displays a blank row at its bottom, marked with an asterisk, and the user can enter a new row—and therefore a new record in the underlying recordset—simply by typing a character in one of the cells in this row. When this happens, the control fires a BeforeInsert event, immediately followed by an AfterInsert event (unless you cancel the command), and then an OnAddNew event. The exact event sequence is as follows:

BeforeInsert	The user clicks on the last row.
AfterInsert
OnAddNew
RowColChange	This event fires only when the move is complete.
BeforeColEdit	The user types a key.
ColEdit
Change
Other Change and Keyxxx events
BeforeColUpdate	The user moves to another column on the same row.
AfterColUpdate
AfterColEdit
RowColChange	This event fires only when the move is complete. The user enters values in other cells on the same row.
BeforeUpdate	The user moves to another row.
AfterUpdate
RowColChange	This event fires only when the move is complete.

You can monitor the current status using the AddNewMode property, which can be assigned one of the following values: 0-dbgNoAddNew (no AddNew command is in progress), 1-dbgAddNewCurrent (the current cell is on the last row, but no AddNew command is pending), 2-dbgAddNewPending (the current row is in the next-to-last row as a result of a pending AddNew command). An AddNew command can be initiated either by the user or by code, as the result of assignment to the Text or Value properties.

The DataGrid control exposes all the usual mouse events, which are passed the mouse coordinates and the state of the shift keys. Unfortunately, the DataGrid control doesn't support OLE drag-and-drop operations, so you won't find the usual OLExxxx properties, methods, and events. When working with the mouse, you're likely to use three methods exposed by the control: the RowContaining method, which returns the visible row over which the mouse cursor is located; the ColContaining method, which returns the corresponding column number; and finally the SplitContaining method, which returns the split number. If the mouse is outside the grid area—for example, when the mouse is over the record selectors area—these methods return -1. Here is an example that uses the ToolTipText property to display a ToolTip with the underlying value of the cell under the mouse, which can be especially useful if the column is too narrow to display longer strings:

Private Sub DataGrid1_MouseMove(Button As Integer, Shift As Integer, _ X As Single, Y As Single) Dim row As Long, col As Long On Error Resume Next row = DataGrid1.RowContaining(Y) col = DataGrid1.ColContaining(X) If row >= 0 And col >= 0 Then DataGrid1.ToolTipText = DataGrid1.Columns(col).CellValue _ (DataGrid1.RowBookmark(row)) Else DataGrid1.ToolTipText = "" End If End Sub

You can programmatically change the layout of a DataGrid control by using one of the many properties and methods of the Splits and Columns collections. For example, you can add a new column using the Columns.Add method, as follows:

' Add a Product Name column. (It will become the 4th column.) With DataGrid1.Columns.Add(3) .Caption = "Product Name" .DataField = "ProductName" End With ' You need to rebind the grid after adding a bound column. DataGrid1.ReBind

You can also remove a column from the layout, using the Columns.Remove method:

' Remove the column added by the previous code snippet. DataGrid1.Columns.Remove 3

Adding a split requires that you use the Splits.Add method. The argument you pass to this method is the position of the new split (0 for the leftmost split in the grid):

' Add a new split to the left of all existing splits. DataGrid1.Splits.Add 0

After you create a split, you have to decide which columns are visible in it. Because each new split inherits all the columns from the grid, removing a column from one split would remove it from all the other splits, as described in the "Editing the Column Layout" section earlier in this chapter. Rather than deleting unwanted columns, make them invisible, as illustrated by the following code:

' Add a new split to the right of the existing split. With DataGrid1.Splits.Add(1) ' Ensure that the two splits divide the grid's width in half. ' Assumes that the existing split's SizeMode property is 0-dbgScalable. ' (Always set SizeMode before Size!) .SizeMode = dbgScalable .Size = DataGrid1.Splits(0).Size ' This new split can be scrolled independently. .ScrollGroup = DataGrid1.Splits(0).ScrollGroup + 1 ' Hide all the columns except the one labeled "ProductName". For Each gcol In .Columns gcol.Visible = (gcol.Caption = "ProductName") Next End With

Often a value retrieved from a database table isn't meaningful in itself and is only useful because it's a foreign key to another table where the real information is. For example, the Products table in NWind.mdb includes a SupplierID field, which contains the value of a key in the Suppliers table, where you can find the name and the address of the supplier for that particular product. When you're displaying the Products table in a DataGrid control, you might use a suitable JOIN statement for the ADO Data control's RecordSource property so that the grid automatically displays the correct supplier name instead of its key.

The ADO binding mechanism, however, provides you with a better alternative. The trick is to declare a custom StdDataFormat object, assign it to the DataFormat property of a Column object, and then use the Format event to transform the numeric key values coming from the data source into more descriptive strings of text. The following routine loads all the values from the secondary table (also known as the lookup table) into a hidden ComboBox control. The routine then uses the contents of that control in the Format event of the custom StdDataFormat object to translate the SupplierID key into the supplier's CompanyName field:

Dim WithEvents SupplierFormat As StdDataFormat Private Sub Form_Load() ' Load all the values from the Supplier lookup table into the ' hidden cboSuppliers ComboBox control. Dim rs As New ADODB.Recordset rs.Open "Suppliers", Adodc1.Recordset.ActiveConnection Do Until rs.EOF cboSuppliers.AddItem rs("CompanyName") ' The SupplierID value goes into the ItemData property. cboSuppliers.ItemData(cboSuppliers.NewIndex) = rs("SupplierID") rs.MoveNext Loop rs.Close ' Assign the custom format object to the SupplierID column. Set SupplierFormat = New StdDataFormat Set DataGrid1.Columns("SupplierID").DataFormat = SupplierFormat ' Make the row height equal to the ComboBox's height. DataGrid1.RowHeight = cboSuppliers.Height End Sub Private Sub SupplierFormat_Format(ByVal DataValue As _ StdFormat.StdDataValue) Dim i As Long ' Search the key value in the cboSuppliers ComboBox. For i = 0 To cboSuppliers.ListCount - 1 If cboSuppliers.ItemData(i) = DataValue Then DataValue = cboSuppliers.List(i) Exit For End If Next End Sub

Using the ComboBox control as a repository for the contents of the lookup table isn't a casual decision. In fact, with some wizardry we can even use the ComboBox to let the user select a new value for the SupplierID field. All we have to do is make the ComboBox control appear in front of the DataGrid control, exactly over the cell edited by the user, and then update the underlying SupplierID field when the user selects a new value from the list. For the best visual effect, you also need to trap a few events so that the ComboBox is always in the correct position, as in Figure 15-4. Here's the code that does the trick:

Private Sub MoveCombo() ' In case of error, hide the ComboBox. On Error GoTo Error_Handler Dim gcol As MSDataGridLib.Column Set gcol = DataGrid1.Columns(DataGrid1.col) If gcol.Caption = "SupplierID" And DataGrid1.CurrentCellVisible Then ' Move the ComboBox inside the SupplierID column ' if it is the current column and it is visible. cboSuppliers.Move DataGrid1.Left + gcol.Left, _ DataGrid1.Top + DataGrid1.RowTop(DataGrid1.row), gcol.Width cboSuppliers.ZOrder cboSuppliers.SetFocus cboSuppliers.Text = gcol.Text Exit Sub End If Error_Handler: ' In all other cases, hide the ComboBox. cboSuppliers.Move _10000 If DataGrid1.Visible Then DataGrid1.SetFocus End Sub Private Sub cboSuppliers_Click() ' Change the value of the underlying grid cell. DataGrid1.Columns("SupplierID").Value = _ cboSuppliers.ItemData(cboSuppliers.ListIndex) End Sub Private Sub DataGrid1_RowColChange(LastRow As Variant, _ ByVal LastCol As Integer) MoveCombo End Sub Private Sub DataGrid1_RowResize(Cancel As Integer) MoveCombo End Sub Private Sub DataGrid1_ColResize(ByVal ColIndex As Integer, _ Cancel As Integer) MoveCombo End Sub Private Sub DataGrid1_Scroll(Cancel As Integer) MoveCombo End Sub Private Sub DataGrid1_SplitChange() MoveCombo End Sub

This code requires that the DataGrid control's RowHeight property match the ComboBox's Height property. Because the latter is read-only at run time, execute the following statement in the Form_Load event procedure:

' Have the row height match the ComboBox's height. DataGrid1.RowHeight = cboSuppliers.Height

Another approach to lookup tables is based on the Button property of the Column object and the ButtonClick event. In this case, however, you get a better visual result if you display a ListBox (or DataList) control just under the current cell, rather than displaying a ComboBox or DataCombo control over the cell. Since the implementation of this latter method is similar to what I've shown previously, I leave it to you as an exercise.

Figure 15-4. The demonstration application uses lookup fields with drop-down ComboBoxes and supports splits, sort commands, and more.

The DataGrid control doesn't offer any built-in functionality for sorting data. However, thanks to its HeadClick event and the ADO Recordset's Sort property, sorting data is an easy task that requires only a handful of statements:

Private Sub DataGrid1_HeadClick(ByVal ColIndex As Integer) ' Sort on the clicked column. Dim rs As ADODB.Recordset Set rs = Adodc1.Recordset If rs.Sort <> DataGrid1.Columns(ColIndex).DataField & " ASC" Then ' Sort in ascending order; this block is executed if the ' data isn't sorted, is sorted on a different field, ' or is sorted in descending order. rs.Sort = DataGrid1.Columns(ColIndex).DataField & " ASC" Else ' Sort in descending order. rs.Sort = DataGrid1.Columns(ColIndex).DataField & " DESC" End If ' No need to refresh the contents of the DataGrid. End Sub

The only limitation of this approach is that it doesn't work well if the column contains lookup values.